﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;

namespace GiaoDien
{
    class NguoiDungDAO
    {
        #region themThongTin
        public static bool themNguoiDung(NguoiDungDTO ndDTO)
        {
            string sQuery = "insert into ChitietSP(Masach,Tensach,Tacgia,NhaXB,Kichthuoc,Sotrang,Ngaysanxuat,Soluongcon,Dongia) values(" + ndDTO.MaSach + ",'" + ndDTO.TenSach + "','" +
                ndDTO.TacGia + "','" + ndDTO.NhaXB + "','" + ndDTO.KichThuoc + "','" + ndDTO.SoTrang + "','" + ndDTO.NgaySanXuat + "','" + ndDTO.SoLuongCon + "','" + ndDTO.DonGia + "')";
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
        #endregion
		
		#region themNhanVien
        public static bool themNhanVien(NguoiDungDTO ndDTO)
        {
            string sQuery = "insert into NhanVien(Manhanvien,Tennhanvien,Ngaysinh,Diachi,Loaichucvu) values(" + ndDTO.MaNhanVien + ",'" + ndDTO.TenNhanVien + "','" +
                ndDTO.NgaySinh + "','" + ndDTO.DiaChi + "'," + ndDTO.LoaiChucVu + ")";
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
        #endregion
		
		#region themQuyDinh
        public static bool themQuyDinh(NguoiDungDTO ndDTO)
        {
            string sQuery = "insert into QuiDinh(Maquidinh,Tenquidinh,Noidung) values(" + ndDTO.MaQuiDinh + ",'" + ndDTO.TenQuiDinh + "','" +
                ndDTO.NoiDung + "')";
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
        #endregion

		#region ThemSP
		public static bool ThemSP(NguoiDungDTO ndDTO)
        {
            string sQuery = "insert into SanPham(Masach,Tensach,Loaisach) values(" + ndDTO.MaSach + ",'" + ndDTO.TenSach + "','" +
                ndDTO.LoaiSach + "')";
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
		#endregion
		
		#region ThemTacGia
		public static bool ThemTacGia(NguoiDungDTO ndDTO)
        {
            string sQuery = "insert into TacGia(Matacgia,Tentacgia) values(" + ndDTO.MaTacGia + ",'" + ndDTO.TenTacGia + "')";
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
		#endregion
		
        #region LayTacGia
        public static DataTable LayTacGia(NguoiDungDTO ndDTO)
        {
            string sQuery = "select Tentacgia from TacGia order by Matacgia";
            OleDbConnection con = DataProvider.taoKetNoi();
            DataTable da = DataProvider.layDataTable(sQuery, con);
            DataProvider.dongKetNoi(con);
            return da;
        }
        #endregion

        #region layMaSach
        public static string LayMaSach(NguoiDungDTO ndDTO)
        {
            string sQuery = "select top 1 MaSach from ChitietSP order by MaSach DESC";
            OleDbConnection con = DataProvider.taoKetNoi();
            DataTable da = DataProvider.layDataTable(sQuery, con);
            DataProvider.dongKetNoi(con);
            DataRow dr = da.Rows[0];
            string MaSachMoi=dr["Masach"].ToString();
            return (int.Parse(MaSachMoi) + 1).ToString();
        }
        #endregion

        #region LayChiTietSanPham
        public static DataTable LayChiTietSanPham(NguoiDungDTO ndDTO)
        {
            string sQuery = "select * from ChitietSP order by MaSach";
            OleDbConnection con = DataProvider.taoKetNoi();
            DataTable da = DataProvider.layDataTable(sQuery, con);
            DataProvider.dongKetNoi(con);
            return da;
        }
        #endregion
		
		#region LayChiTietNhanVien
        public static DataTable LayChiTietNhanVien(NguoiDungDTO ndDTO)
        {
            string sQuery = "select * from NhanVien order by Manhanvien";
            OleDbConnection con = DataProvider.taoKetNoi();
            DataTable da = DataProvider.layDataTable(sQuery, con);
            DataProvider.dongKetNoi(con);
            return da;
        }
        #endregion
		
		#region LayChiTietQuyDinh
        public static DataTable LayChiTietQuyDinh(NguoiDungDTO ndDTO)
        {
            string sQuery = "select * from QuiDinh order by Maquidinh";
            OleDbConnection con = DataProvider.taoKetNoi();
            DataTable da = DataProvider.layDataTable(sQuery, con);
            DataProvider.dongKetNoi(con);
            return da;
        }
        #endregion
		
        #region CapNhapThongTin
        public static bool CapNhapThongTin(NguoiDungDTO ndDTO)
        {
            string sQuery = "update ChitietSP set Tensach='" + ndDTO.TenSach + "',Tacgia='" + ndDTO.TacGia +
                + "',NhaXB='" + ndDTO.NhaXB + "',Kichthuoc='" + ndDTO.KichThuoc + "',Sotrang='" + ndDTO.SoTrang
                + "',Ngaysanxuat='" + ndDTO.NgaySanXuat + "',Soluongcon='" + ndDTO.SoLuongCon + "',Dongia='" + ndDTO.DonGia + "' where Masach=" + ndDTO.MaSach;
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
        #endregion
		
		#region CapNhapSP
		public static bool CapNhapSP(NguoiDungDTO ndDTO)
        {
            string sQuery = "update SanPham set Tensach='" + ndDTO.TenSach + "',Loaisach='" + ndDTO.LoaiSach + "' where Masach=" + ndDTO.MaSach;
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
		#endregion
		
		#region CapNhapTacGia
		public static bool CapNhapTacGia(NguoiDungDTO ndDTO)
        {
            string sQuery = "update TacGia set Tentacgia='" + ndDTO.TenTacGia + "' where Matacgia=" + ndDTO.MaTacGia";
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
		#endregion
		
		#region CapNhapNhanVien
		public static bool CapNhapNhanVien(NguoiDungDTO ndDTO)
        {
            string sQuery = "update NhanVien set Tennhanvien='" + ndDTO.TenQuiDinh + "',Ngaysinh='" + ndDTO.NgaySinh + "',Diachi='" + ndDTO.DiaChi + "',Loaichucvu=" + ndDTO.LoaiChucVu + " where Manhanvien=" + ndDTO.MaNhanVien";
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
		#endregion
		
		#region CapNhapQuyDinh
		public static bool CapNhapQuyDinh(NguoiDungDTO ndDTO)
        {
            string sQuery = "update QuiDinh set Tenquidinh='" + ndDTO.TenQuiDinh + "',Noidung='" + ndDTO.NoiDung + "' where Maquidinh=" + ndDTO.MaQuiDinh";
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
		#endregion
		
		#region XoaSanPham
        public static bool XoaSanPham(NguoiDungDTO ndDTO)
        {
            string sQuery = "delete from ChiTietSP where Masach=" + ndDTO.MaSach;
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
        #endregion
		
		#region XoaTacGia
		public static bool XoaTacGia(NguoiDungDTO ndDTO)
        {
            string sQuery = "delete from TacGia where Matacgia=" + ndDTO.MaTacGia";
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
		#endregion
		
		#region XoaNhanVien
		public static bool XoaNhanVien(NguoiDungDTO ndDTO)
        {
            string sQuery = "delete from NhanVien where Manhanvien=" + ndDTO.MaNhanVien";
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
		#endregion
		
		#region XoaQuyDinh
		public static bool XoaQuyDinh(NguoiDungDTO ndDTO)
        {
            string sQuery = "delete from QuiDinh where Maquidinh=" + ndDTO.MaQuiDinh";
            OleDbConnection con = DataProvider.taoKetNoi();
            bool ketqua = DataProvider.executeNonQuery(sQuery, con);
            DataProvider.dongKetNoi(con);
            return ketqua;
        }
		#endregion
    }
}
